﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;

using Includes;

namespace SyncroneModule
{
   /// <summary>
   /// Mysql database implementation of the persistancy layer interface
   /// </summary>
   public class PersistSync : IPersistSync
   {  
      // Database settings
      private const String myConnectionString = @"SERVER=localhost;
                                                  DATABASE=homecaresystems;
                                                  UID=hcs;
                                                  PASSWORD=hcs_starting1st;";

      private List<CallInfoSync> listOfPeople;
      private MySqlConnection connection;


      /// <summary>
      /// Clears the informed flags of all patients whose next test time is less than timeDif
      /// </summary>
      /// <param name="timeDif"> Maximum distance to test time in seconds </param>
      public void clearInformedFlags(uint timeDif)
      {
         // Highest reachable time (for example if time of check is 0:00 and current time is 23:59)
//         const UInt32 MAXTIME = 24*60*60;

         // Check Argument for validity
         if(timeDif < 0 || timeDif > 3600)
            throw new ArgumentException("Time difference must not be negative, and no longer than 1 hour");

         connect();

         // TODO - Get rid of midnight bug
         // Clear flags in all cases, where the next time for vital data transmission is less than timeDif seconds away
         MySqlCommand command = connection.CreateCommand();
         command.CommandText = @"UPDATE Steuerdaten s, Patientendaten p
                                 SET s.Informiert = 0 
                                 WHERE 
                                 s.id = p.id AND
                                 p.status = 1 AND
                                 (
                                 ((TIME_TO_SEC(p.Uberprufung1) - TIME_TO_SEC(NOW())) <=  " + timeDif +              @" AND (TIME_TO_SEC(p.Uberprufung1) - TIME_TO_SEC(NOW()) >= 0)) OR
                                 ((TIME_TO_SEC(p.Uberprufung2) - TIME_TO_SEC(NOW())) <=  " + timeDif +              @" AND (TIME_TO_SEC(p.Uberprufung2) - TIME_TO_SEC(NOW()) >= 0))
                                 );";
         command.ExecuteNonQuery();
         
         disconnect();
      }

      /// <summary>
      /// Gets list of numbers that overdue
      /// </summary>
      /// <param name="timeDif"> Minimum overdue time in seconds</param>
      /// <returns> Name of patient and his TelNumber </returns>
      public CallInfoSync[] getTelListInfoLate(uint timeDif)
      {
         // Highest reachable time (for example if time of check is 0:00 and current time is 23:59)
//         const UInt32 MAXTIME = 24 * 60 * 60;
         
         // Min test distance
         const UInt32 MARGIN = 6*60*60;

         listOfPeople = new List<CallInfoSync>();

         connect();

         // TODO - Get rid of midnight bug
         // Get all needed information
         MySqlCommand command = connection.CreateCommand();
         command.CommandText = @"SELECT m.TEL,p.Nachname,p.Vorname
                                FROM Mapping m, Patientendaten p, Steuerdaten s
                                WHERE
                                m.ID = p.ID AND
                                m.ID = s.ID AND
                                s.Informiert = 0 AND
                                p.Status = 1 AND
                                (
                                ((TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung1)) >=  " + timeDif + @" AND (TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung1) <= " + MARGIN + @")) OR
                                ((TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung2)) >=  " + timeDif + @" AND (TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung2) <= " + MARGIN + @"))
                                );";
          
          // Copy results to resultlist
          MySqlDataReader Reader = command.ExecuteReader();
          while (Reader.Read())
          {
              try
              {
                  CallInfoSync tmp = new CallInfoSync();
                  tmp.number = Reader.GetString("TEL");
                  tmp.name   = Reader.GetString("Vorname") + " " + Reader.GetString("Nachname");
                  listOfPeople.Add(tmp);
              }
              catch (FormatException)
              {
                  addLog("000000000000000", "Error while fetching Late with timedif " + timeDif);
              }
          }

          disconnect();

          // Return results
          return listOfPeople.ToArray();
      }

      /// <summary>
      /// Gets list of next of kin numbers for patients that are long overdue
      /// </summary>
      /// <param name="timeDif"> Minimum overdue time in seconds</param>
      /// <returns> Name of patient and number of his next of kin </returns>
      public CallInfoSync[] getTelListInfoVeryLate(uint timeDif)
      {
         // Highest reachable time (for example if time of check is 0:00 and current time is 23:59)
//         const UInt32 MAXTIME = 24 * 60 * 60;

         // Min test distance
         const UInt32 MARGIN = 6 * 60 * 60;
         
         listOfPeople = new List<CallInfoSync>();

         connect();

         // TODO - Get rid of midnight bug
         // Get all needed information in one masterfully written query...
         MySqlCommand command = connection.CreateCommand();
         command.CommandText = @"SELECT p.TelKontakt2, p.Nachname, p.Vorname 
                                FROM Mapping m, Patientendaten p, Steuerdaten s
                                WHERE
                                m.ID = p.ID AND
                                m.ID = s.ID AND
                                s.Informiert = 0 AND
                                p.Status = 1 AND
                                (
                                ((TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung1)) >=  " + timeDif + @" AND (TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung1) <= " + MARGIN + @")) OR
                                ((TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung2)) >=  " + timeDif + @" AND (TIME_TO_SEC(NOW()) - TIME_TO_SEC(p.Uberprufung2) <= " + MARGIN + @"))
                                );";

         // Copy results to resultlist
         MySqlDataReader Reader = command.ExecuteReader();
         while (Reader.Read())
         {
            try
            {
               CallInfoSync tmp = new CallInfoSync();
               tmp.number = Reader.GetString("TelKontakt2");
               tmp.name   = Reader.GetString("Vorname") + " " + Reader.GetString("Nachname");
               listOfPeople.Add(tmp);
            }
            catch (FormatException)
            {
               addLog("000000000000000", "Error while fetching Very Late with timedif " + timeDif);
            }
         }

          disconnect();

          return listOfPeople.ToArray();
      }


      /// <summary>
      /// Gets list of next of kin numbers for patients that have been called to the Hospital but not yet arrived
      /// </summary>
      /// <param name="timeDif"> Minimum overdue time in seconds</param>
      /// <returns> Name of patient and number of his next of kin </returns>
      public CallInfoSync[] getTelListKR(uint timeDif)
      {
          listOfPeople = new List<CallInfoSync>();

          connect();

          // TODO - Get rid of midnight bug
          // Get all needed information
          MySqlCommand command = connection.CreateCommand();
          command.CommandText = @"SELECT m.ID, p.TelKontakt2, p.Nachname, p.Vorname
                                  FROM Mapping m, Patientendaten p, Steuerdaten s
                                  WHERE 
                                  m.ID = p.ID AND 
                                  m.ID = s.ID AND 
                                  s.KR = 1     AND 
                                  p.Status = 1 AND 
                                  (
                                  (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP((KR_Timestamp))) >= " + timeDif + @"))
                                  );";

         // Copy results to resultlist
         MySqlDataReader Reader = command.ExecuteReader();
         while (Reader.Read())
         {
            try
            {
               CallInfoSync tmp = new CallInfoSync();
               tmp.number = Reader.GetString("TelKontakt2");
               tmp.name   = Reader.GetString("Vorname") + " " + Reader.GetString("Nachname");
               listOfPeople.Add(tmp);
            }
            catch (FormatException)
            {
               addLog("000000000000000", "Error while fetching KR with timedif " + timeDif);
            }
         }

          disconnect();

          return listOfPeople.ToArray();
      }

      /// <summary>
      /// Get Flags for specified patient
      /// </summary>
      /// <param name="tel"> Patients number </param>
      /// <returns> Flagset for the patient </returns>
      public Flags getFlags(String tel)
      {
         Flags flags = new Flags();

         connect();
         
         // Get the flags
         MySqlCommand command = connection.CreateCommand();
         command.CommandText = @"SELECT s.Informiert, s.SuccessiveD, s.KR
                                 FROM Mapping m, Steuerdaten s, Patientendaten p
                                 WHERE 
                                 m.ID = s.ID AND
                                 m.ID = p.ID AND
                                 m.TEL = '" + tel + @"' AND 
                                 p.Status = 1;";
         
         // Copy to result
         MySqlDataReader Reader = command.ExecuteReader();
         Reader.Read();
         try
         {
            flags.info = Reader.GetBoolean("Informiert");
            flags.rd   = Reader.GetBoolean("SuccessiveD");
            flags.kr   = Reader.GetBoolean("KR");
         }
         catch (Exception)
         {
            addLog("000000000000000", "Error while fetching flags with tel: " + tel);
         }

          disconnect();

          return flags;
      }


      /// <summary>
      /// Set flags for the specified patient
      /// </summary>
      /// <param name="tel"> Number of patient </param>
      /// <param name="flags"> Flags to set </param>
      public void setFlags(String tel, Flags flags)
      {
        connect();

        // Set flags
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = @"UPDATE Steuerdaten s, Mapping m, Patientendaten p
                                SET Informiert = " + (flags.info ? 1 : 0) + @",
                                    KR = "         + (flags.kr ? 1 : 0)   + @",
                                    SuccessiveD = "+ (flags.rd ? 1 : 0)   + 
                               @" WHERE 
                                  m.TEL = '" + tel + @"' AND 
                                  m.ID  = s.ID AND 
                                  m.ID  = p.ID AND
                                  p.status = 1";

        command.ExecuteNonQuery();

        disconnect();
      }

      
      /// <summary>
      /// Adds a log to the log table
      /// </summary>
      /// <param name="tel"> Number to log for </param>
      /// <param name="log"> Text to log </param>
      public void addLog(String tel, String log)
      {
          connect();

          // Translate Tel to ID
          MySqlCommand command = connection.CreateCommand();
          command.CommandText  = @"SELECT m.ID
                                   FROM  Mapping m, Patientendaten p 
                                   WHERE 
                                   m.TEL = '" + tel + @"' AND   
                                   m.ID  = p.ID AND   
                                   p.Status = 1;";

          int id;

          try 
          { 
            id = int.Parse(command.ExecuteScalar().ToString()); 
          }
          catch(Exception) 
          { 
            id = 0; 
          }

          // Insert log
          command.CommandText = @"INSERT INTO Log (ID, Timestamp, Eintrag)
                                  VALUES (" + id + ", CURRENT_TIMESTAMP, '" + log + "');";

          command.ExecuteNonQuery();

          disconnect();

          return;
      }



      private void connect()
      {
          connection = new MySqlConnection(myConnectionString);
          try
          {
              connection.Open();
          }
          catch (MySqlException e)
          {
              throw e;
          }
      }

      private void disconnect()
      {
          connection.Close();
      }
   }
}
